import org.apache.ignite.internal.util.IgniteUtils;
import org.junit.Test;

import java.sql.*;

/**
 * @author dinghao
 * @create 2021-09-18 17:11
 * @message
 */
public class IgniteTest {
    @Test
    public void test1() throws Exception {
        print("JDBC example started.");

        // 创建jdbc连接
        try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/")) {
            print("Connected to server.");

            // 创建数据库对象
            try (Statement stmt = conn.createStatement()) {
                // 创建基于复制模式的CIty表
                stmt.executeUpdate("CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) " +
                        "WITH \"template=replicated\"");

                // 创建带有一个备份基于分片模式的person表
                stmt.executeUpdate("CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, " +
                        "PRIMARY KEY (id, city_id)) WITH \"backups=1, affinity_key=city_id\"");

                // 创建索引
                stmt.executeUpdate("CREATE INDEX on Person (city_id)");
            }

            print("Created database objects.");

            // 往city表中插入数据
            try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO city (id, name) VALUES (?, ?)")) {
                stmt.setLong(1, 1L);
                stmt.setString(2, "Forest Hill");
                stmt.executeUpdate();

                stmt.setLong(1, 2L);
                stmt.setString(2, "Denver");
                stmt.executeUpdate();

                stmt.setLong(1, 3L);
                stmt.setString(2, "St. Petersburg");
                stmt.executeUpdate();
            }

            // 往person表中插入数据
            try (PreparedStatement stmt =
                         conn.prepareStatement("INSERT INTO person (id, name, city_id) values (?, ?, ?)")) {
                stmt.setLong(1, 1L);
                stmt.setString(2, "John Doe");
                stmt.setLong(3, 3L);
                stmt.executeUpdate();

                stmt.setLong(1, 2L);
                stmt.setString(2, "Jane Roe");
                stmt.setLong(3, 2L);
                stmt.executeUpdate();

                stmt.setLong(1, 3L);
                stmt.setString(2, "Mary Major");
                stmt.setLong(3, 1L);
                stmt.executeUpdate();

                stmt.setLong(1, 4L);
                stmt.setString(2, "Richard Miles");
                stmt.setLong(3, 2L);
                stmt.executeUpdate();
            }

            print("Populated data.");

            // 查询数据
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs =
                             stmt.executeQuery("SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")) {
                    print("Query results:");

                    while (rs.next()) {
                        System.out.println(">>>    " + rs.getString(1) + ", " + rs.getString(2));
                    }
                }
            }

            // 清楚表数据
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("DROP TABLE Person");
                stmt.executeUpdate("DROP TABLE City");
            }

            print("Dropped database objects.");
        }

        print("JDBC example finished.");
    }

    @Test
    public void test2() throws Exception {
        // 创建jdbc连接
        try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/")) {
            print("Connected to server.");

            // 建表
            executeCommand(conn, "DROP TABLE IF EXISTS City");

            executeCommand(conn,
                    "CREATE TABLE City (" +
                            "    ID INT(11), " +
                            "    Name CHAR(35), " +
                            "    CountryCode CHAR(3), " +
                            "    District CHAR(20), " +
                            "    Population INT(11), " +
                            "    PRIMARY KEY (ID, CountryCode) " +
                            ") WITH \"template=partitioned, backups=1, affinityKey=CountryCode, CACHE_NAME=City\""
            );

            print("Created database objects.");

            // 从csv文件中加载数据
            executeCommand(conn, "COPY FROM '" +
                    IgniteUtils.resolveIgnitePath("src/main/resources/sql/city.csv") + "' " +
                    "INTO City (ID, Name, CountryCode, District, Population) FORMAT CSV");

            // 读取数据
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM City")) {
                    rs.next();

                    print("Populated City table: " + rs.getLong(1) + " entries");
                }
            }

            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery("SELECT Name, CountryCode FROM City WHERE ID=5")) {
                    rs.next();

                    print("City with ID=5: " + rs.getString(1) + ", " + rs.getString(2));
                }
            }

            // 删除数据
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("DROP TABLE City");
            }

            print("Dropped database objects.");
        }
    }
    /**
     * Prints message.
     *
     * @param msg Message to print before all objects are printed.
     */
    private static void print(String msg) {
        System.out.println();
        System.out.println(">>> " + msg);
    }

    private static void executeCommand(Connection conn, String sql) throws Exception {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        }
    }
}
